//
//  LJHFMData.m
//  HEARME
//
//  Created by mac on 16/2/24.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "LJHFMData.h"

@implementation LJHFMData

+(instancetype)shareFMDB
{
    static LJHFMData *fmdb = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        
        fmdb = [[LJHFMData alloc] init];
        
    });
    
    return fmdb;
}


-(void)openDB
{
    
    NSString *fileName=[DOCUMENT stringByAppendingPathComponent:@"douBan2.0.sqlite"];
    NSLog(@"fileName = %@",fileName);
    
    //2.获得数据库
    self.db = [FMDatabase databaseWithPath:fileName];
    
}


-(void)openDBandCreate
{
    //导入sqlite框架，导入FMDB文件夹
    
    //1.获得数据库文件的路径
    
    NSString *fileName=[DOCUMENT stringByAppendingPathComponent:@"seagullFM.sqlite"];
    NSLog(@"fileName = %@",fileName);
    
    //2.获得数据库
    self.db = [FMDatabase databaseWithPath:fileName];
    
    //3.打开数据库
    if ([self.db open]) {
        NSLog(@"ok");
        
        //4.创建专辑表
        int result=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS albums (albumId INTEGER PRIMARY KEY , albumName TEXT NOT NULL, albumPic TEXT NOT NULL, albumIntro TEXT NOT NULL, albumTracks TEXT NOT NULL, myTitle TEXT NOT NULL);"];
        
        if (result) {
            NSLog(@"创表成功");
        }else{
            NSLog(@"创表失败  %d", result);
        }
        //声音表
        int result1=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS tracks (trackId TEXT PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike INTEGER NOT NULL);"];
        
        if (result1) {
            NSLog(@"创表成功");
        }else{
            NSLog(@"创表失败  %d", result1);
        }
        
        
    }
    [self.db close];
}

#pragma mark 销毁表格
-(void)dropTableAlbums
{
    [self.db open];
    [self.db executeUpdate:@"DROP TABLE IF EXISTS albums"];
    
    int result=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS albums (albumId INTEGER PRIMARY KEY , albumName TEXT NOT NULL, albumPic TEXT NOT NULL, albumIntro TEXT NOT NULL, albumTracks TEXT NOT NULL, myTitle TEXT NOT NULL);"];
    
    if (result) {
        NSLog(@"创表成功");
    }else{
        NSLog(@"创表失败  %d", result);
    }
    [self.db close];
}
-(void)dropTableTracks
{
    [self.db open];
    [self.db executeUpdate:@"DROP TABLE IF EXISTS tracks"];
    
    int result=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS tracks (trackId TEXT PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike INTEGER NOT NULL);"];
    
    if (result) {
        NSLog(@"创表成功");
    }else{
        NSLog(@"创表失败  %d", result);
    }
    [self.db close];
}



#pragma mark 插入数据
-(void)insertAlbumData:(LJHAlbumModel *)album
{
    [self.db open];
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO albums(albumId, albumName, albumPic, albumIntro, albumTracks, myTitle) VALUES('%@','%@','%@', '%@', '%@', '%@')",album.myId, album.title, album.albumCoverUrl290,album.intro, album.tracksCounts,album.albumTitle];
    
    int result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入成功");
        
    }else
    {
        NSLog(@"插入失败 %d", result);
        
    }
    [self.db close];
}

-(void)insertTrackData:(LJHAlbumModel *)track
{
    [self.db open];
    
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO tracks(trackId, trackName, trackPic, trackIntro, trackPlay, trackLike) VALUES('%@','%@','%@', '%@', '%ld', '%ld')", track.trackId, track.title, track.coverSmall,track.nickname, track.playsCounts,track.likes];
    
    int result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入成功");
        
    }else
    {
        NSLog(@"插入失败 %d", result);
        
    }
    [self.db close];
}


#pragma mark 查询数据
-(NSMutableArray *)selectAlbumData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from albums"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //albumId INTEGER PRIMARY KEY , albumName TEXT NOT NULL, albumPic TEXT NOT NULL, albumIntro TEXT NOT NULL, albumTracks TEXT NOT NULL, myTitle TEXT NOT NULL
        
        NSString *idNum = [resultSet objectForColumnName:@"albumId"];
        NSString *title = [resultSet objectForColumnName:@"albumName"];
        NSString *pic = [resultSet objectForColumnName:@"albumPic"];
        NSString *info = [resultSet objectForColumnName:@"albumIntro"];
        NSString *trackNum = [resultSet objectForColumnName:@"albumTracks"];
        NSString *myTitle = [resultSet objectForColumnName:@"myTitle"];
        
        LJHChildListModel *model = [[LJHChildListModel alloc] init];
        model.title = title;
        model.intro = info;
        model.coverMiddle = pic;
        model.tracks = trackNum;
        model.albumTitle = myTitle;
        model.albumId = idNum;
        
        [array addObject:model];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}
-(NSMutableArray *)selectTrackData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //    (trackId INTEGER PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike TEXT NOT NULL)
        NSString *idNum = [resultSet objectForColumnName:@"trackId"];
        NSString *title = [resultSet objectForColumnName:@"trackName"];
        NSString *pic = [resultSet objectForColumnName:@"trackPic"];
        NSString *info = [resultSet objectForColumnName:@"trackIntro"];
        
        
        LJHChildListModel *model = [[LJHChildListModel alloc] init];
        model.title = title;
        model.trackId = [idNum integerValue];
        model.coverPath = pic;
        model.subtitle = info;
        
        
        [array addObject:model];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}

-(NSMutableArray *)selectTrackImageData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //    (trackId INTEGER PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike TEXT NOT NULL)
        
        NSString *pic = [resultSet objectForColumnName:@"trackPic"];
        
        
        LJHChildListModel *model = [[LJHChildListModel alloc] init];
        model.coverPath = pic;
        
        [array addObject:model];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}

-(NSMutableArray *)selectTrackIDData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //    (trackId INTEGER PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike TEXT NOT NULL)
        NSString *idNum = [resultSet objectForColumnName:@"trackId"];
        
        LJHAlbumModel *model = [[LJHAlbumModel alloc] init];
        model.trackId = idNum;
        //
        
        [array addObject:model.trackId];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}

-(NSMutableArray *)selectTrackNameData
{
    [self.db open];
    
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from tracks"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        //    (trackId INTEGER PRIMARY KEY , trackName TEXT NOT NULL, trackPic TEXT NOT NULL, trackIntro TEXT NOT NULL, trackPlay TEXT NOT NULL, trackLike TEXT NOT NULL)
        NSString *title = [resultSet objectForColumnName:@"trackName"];
        
        LJHAlbumModel *model = [[LJHAlbumModel alloc] init];
        model.title = title;
        //
        
        [array addObject:model.title];
        
        //        NSLog(@"%@", movie);
    }
    
    [self.db close];
    return array;
    
}


#pragma mark 查询数据
-(NSMutableArray *)selectAlbumIdData
{
    [self.db open];
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from albums"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        
        NSString *idNum = [resultSet objectForColumnName:@"albumId"];
        NSString *title = [resultSet objectForColumnName:@"albumName"];
        NSString *pic = [resultSet objectForColumnName:@"albumPic"];
        NSString *info = [resultSet objectForColumnName:@"albumIntro"];
        NSString *trackNum = [resultSet objectForColumnName:@"albumTracks"];
        NSString *myTitle = [resultSet objectForColumnName:@"myTitle"];
        
        LJHChildListModel *model = [[LJHChildListModel alloc] init];
        model.title = title;
        model.intro = info;
        model.coverMiddle = pic;
        model.tracks = trackNum;
        model.albumTitle = myTitle;
        model.albumId = idNum;
        
        [array addObject:idNum];
        
        //        NSLog(@"%@", movie);
    }
    [self.db close];
    return array;
}

#pragma mark 删除数据
-(void)deleteByAlbumId:(NSString *)Id
{
    [self.db open];
    NSString *deleteSql = [NSString stringWithFormat:
                           @"DELETE FROM albums WHERE albumId = '%@'",Id];
    BOOL res = [self.db executeUpdate:deleteSql];
    
    if (res) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    [self.db close];
}

#pragma mark 删除数据
-(void)deleteByTrackId:(NSString *)Id
{
    [self.db open];
    NSString *deleteSql = [NSString stringWithFormat:
                           @"DELETE FROM tracks WHERE trackId = '%@'",Id];
    BOOL res = [self.db executeUpdate:deleteSql];
    
    if (res) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    [self.db close];
}


@end
